﻿using Beryl.OrderChanger;
using Beryl.OrderControllers.DataStructural;
using Beryl.OrderControllers.DBUpdate;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Drawing.Drawing2D;
using System.Linq;
using System.Web;
using System.Web.Services.Description;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace Beryl.OrderControllers
{
    public class mainOrder
    {
        private static mainOrder instance = null;
        private static readonly object uniqueLock = new object();

        public static mainOrder Instance
        {
            get
            {
                lock (uniqueLock)
                {
                    if (instance == null)
                    {
                        instance = new mainOrder();
                    }
                }
                return instance;
            }
        }
        public mainOrder() { }

        public List<string> QueryData(string para)
        {
            List<string> list = new List<string>();
            try
            {
                Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
                var k = jsonArr[0];
                string SoleOrder = k["SoleOrder"].ToString();
                string cSpecifucation = k["cSpecifucation"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();
                string cStatusNo = k["cStatusNo"].ToString();
                string STime = k["STime"].ToString();
                string ETime = k["ETime"].ToString();
                string cStatus = "";

                //string[] oldValue = STime.Split(' ');
                //string[] date = oldValue[0].Split('-');
                //if (int.Parse(date[1]) < 10 || int.Parse(date[2]) < 10)
                //{
                //    date[1] = "0" + date[1];
                //    date[2] = "0" + date[2];
                //}

                DateTime date1 = DateTime.Parse(STime);
                string statr = date1.ToString("yyyy-MM-dd HH:mm:ss");

                DateTime date2 = DateTime.Parse(ETime);
                string endatr = date2.ToString("yyyy-MM-dd HH:mm:ss");
                if (cStatusNo != "")
                {
                    string sqlstaus = string.Format(@"SELECT cStatusNo,cStatusDesc FROM APS_OrderStatus where cStatusDesc = '{0}';",cStatusNo);
                    var statusRow = CommonMethod.Instance.retData(sqlstaus, "", "");
                    if(statusRow.Length > 0)
                    {
                        cStatus = statusRow[0]["cStatusNo"].ToString();
                    }else
                    {
                        throw new Exception("查询失败");
                    }
                }
                else
                {
                    throw new Exception("订单状态为空");
                }

                string matType;
                switch(cProductDepNo)
                {
                    case "Z21":
                        matType = "C02%"; //液态产品
                        break;
                    case "Z61":
                        matType = "C01%"; //固态产品
                        break;
                    default:
                        matType = "";
                        break;
                }

                string sql = string.Format(@"SELECT DISTINCT a.AutoID,a.cPOrderNo,a.MotypeCode,a.Description,a.cMatNo,a.cCusNo,a.cCusName," +
                "CASE WHEN a.cMatComFlag = '1' THEN '已齐套' ELSE '未齐套' END AS cMatComFlag,a.dMatComTime,a.cStaUpdateTime,a.cStatusNo," +
                "a.dPlanQty,a.cMakeTime,d.cBegProDate,d.cEndProDate,c.cStatusDesc,c.cNextStatusDesc,b.cMatName,b.cSpecifucation," +
            	"LEFT(b.cSpecifucation,2) as cType,b.cCapacity,b.dVoltage,b.dDiameter,b.dheight,e.cValue FROM APS_PlanOrder a " +
            	"LEFT JOIN APS_MatData_V b ON a.cMatNo = b.cMatNo LEFT JOIN APS_OrderStatus c ON a.cStatusNo = c.cStatusNo " +
            	"LEFT JOIN APS_MainPlan d ON a.cPOrderNo = d.cPOrderNo LEFT JOIN PMS_MatExtenProp e ON a.cMatNo = e.cMatNo " +
                "WHERE a.cMatNo LIKE '{0}' and a.cStatusNo = '{1}' ", matType,cStatus);
                if(SoleOrder != "")
                {
                    sql += "and a.cPOrderNo = '" + SoleOrder + "'";
                }
                if(cSpecifucation != "")
                {
                    sql += string.Format(@" and b.cSpecifucation like '%{0}%'",cSpecifucation);
                }
                sql += string.Format(@"and cStaUpdateTime >= '{0}' and cStaUpdateTime <= '{1}'", statr, endatr);
                // sql += " ORDER BY cBegProDate,cPOrderNo ";
                sql += "ORDER BY a.cPOrderNo DESC";
                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if(strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(strPicYZ);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        public List<string> MainOrderCal(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                string sOrdersID = jArray[0]["sOrdersID"].ToString();
                string cProductDepNo = jArray[0]["cProductDepNo"].ToString();
                string cMatNos = jArray[0]["cMatNos"].ToString();

                string APS_PlanOrder = string.Format(@"SELECT cPOrderNo,cMatNo,cStatusNo,dPlanQty FROM APS_PlanOrder WHERE " +
                        "AutoID IN ({0});",sOrdersID);
                var APS_PlanOrderRow = CommonMethod.Instance.retData(APS_PlanOrder, "", "");

                string APS_Rework = string.Format(@"SELECT
                        APS_SOrder.cSONo,
                        APS_Rework.cReworkNo,
                        APS_Rework.dQty,
                        APS_Rework.cProRoutingNo as cPRoutingId,
                        APS_Rework.cReworkFlag,
                        PMS_ProcessPath.cProcessCode as cOperationNo,
                        PMS_ProcessPath.cProcessName as cOperationName,
                        PMS_ProcessPath.cProcessSerialNum as cOpSeq,
                        SUM(case PMS_ProcessPara.cKey WHEN '流转时间' then PMS_ProcessPara.cValue else 0 end) as dTransferTime,
                        SUM(case PMS_ProcessPara.cKey WHEN '物料损耗' then PMS_ProcessPara.cValue else 0 end) as cMatRate
                    FROM
                        APS_Rework
                        LEFT JOIN APS_SOrder ON APS_SOrder.cSONo = APS_Rework.cReworkNo
                        LEFT JOIN PMS_ProcessPath ON APS_Rework.cProRoutingNo = PMS_ProcessPath.cProgramCode
                        LEFT JOIN PMS_ProcessPara ON PMS_ProcessPath.cProcessCode = PMS_ProcessPara.cProcessCode
                    WHERE
                        APS_SOrder.AutoID IN ({0})
                    GROUP BY
                        APS_SOrder.cSONo,
                        APS_Rework.cReworkNo,
                        APS_Rework.dQty,
                        APS_Rework.cProRoutingNo,
                        APS_Rework.cReworkFlag,
                        PMS_ProcessPath.cProcessCode,
                        PMS_ProcessPath.cProcessName,
                        PMS_ProcessPath.cProcessSerialNum;",sOrdersID);
                var APS_ReworkRow = CommonMethod.Instance.retData(APS_Rework, "", "");//重工单

                string APS_MainPlan = string.Format(@"SELECT
                        autoId,
                        cPOrderNo,
                        cMatNo,
                        cResGroupNo,
                        dQty,
                        cRgBegDate,
                        cRgEndDate,
                        cProductDepNo
                    FROM
                        APS_MainPlan 
                    WHERE
                        cProductDepNo = '{0}' 
                        AND cRgBegDate >= CONVERT ( VARCHAR ( 10 ), GETDATE( ), 120 );",cProductDepNo);
                var APS_MainPlanRow = CommonMethod.Instance.retData(APS_MainPlan, "", "");//已制定的主计划

                string APS_Resource = string.Format(@"SELECT
                        cResGroupNo,
                        cResourceNo,
                        cMeterType,
                        cCombination,
                        dDiameter,
                        dHeight,
                        cCalendarNo,
                        cShiftNo,
                        cOperationNo,
                        cOperationName 
                    FROM
                        APS_Resource
                    WHERE
                        cProductDepNo= '{0}' AND cOperationName <> '01钉卷' AND cOperationName<>'01钉卷(固态)'
                    ORDER BY
                        cResGroupNo,cResourceNo;",cProductDepNo);
                var APS_ResourceRow = CommonMethod.Instance.retData(APS_Resource, "", "");//资源 & 当前的设备配置

                string APS_ResGroup = string.Format(@"SELECT
                        cResGroupNo,
                        dBigOrderResNum 
                    FROM
                        APS_ResGroup 
                    WHERE
                        cProductDepNo = '{0}';",cProductDepNo);
                var APS_ResGroupRow = CommonMethod.Instance.retData(APS_ResGroup, "", "");//资源组 & 大批量订单分配的资源数量

                string APS_WorkCalendar = string.Format(@"SELECT
                        c.cCalendarNo,
                        c.dCalendarDate,
                        c.cShiftNo,
                        c.cOperationNo,
                        s.cShiftName,
                        s.dStartTime1,
                        s.dEndTime1,
                        s.dStartTime2,
                        s.dEndTime2,
                        s.dWorkTime 
                    FROM
                        APS_Calendar c
                        LEFT JOIN APS_Shift s ON CHARINDEX( s.cShiftNo, c.cShiftNo ) > 0 
                    WHERE
                        c.cEnabled= '1' AND s.cEnabled= '1' 
                        AND c.dCalendarDate > CONVERT ( VARCHAR ( 10 ), GETDATE( ), 120 ) 
                    ORDER BY
                        dCalendarDate,dStartTime1;");
                var APS_WorkCalendarRow = CommonMethod.Instance.retData(APS_WorkCalendar, "", ""); //工作日历

                string APS_Capacity = string.Format(@"SELECT
                        cResourceNo,
                        dDiameter,
                        dHeight,
                        dHeightUp,
                        dSpeed,
                        dHourlyCapacity,
                        dCapacityRatio
                    FROM
                        APS_Capacity
                    ORDER BY
                        cResourceNo,dDiameter;");
                var APS_CapacityRow = CommonMethod.Instance.retData(APS_Capacity, "", "");//产能数据

                string APS_Maintenance = string.Format(@"SELECT
                        cDeviceNo,
                        dStartTime,
                        dEndTime
                    FROM
                        EM_MaintenancePlan
                    WHERE
                        DATEDIFF( DAY, getdate( ), dStartTime ) > 0;");

                //string APS_Maintenance = string.Format(@"SELECT
                //        cDeviceNo,
                //        dStartTime,
                //        dEndTime
                //    FROM
                //        EM_MaintenancePlan;");

                var APS_MaintenanceRow = CommonMethod.Instance.retData(APS_Maintenance, "", "");//资源维保计划

                string APS_MatData_V = string.Format(@"SELECT
                        APS_MatData_V.cMatNo,
                        APS_MatData_V.cUnit,
                        APS_MatData_V.dwidth,
                        APS_MatData_V.dVoltage,
                        APS_MatData_V.Unit,
                        APS_MatData_V.cMatName,
                        APS_MatData_V.cSpecifucation,
                        APS_MatData_V.cCateNo,
                        APS_MatData_V.cCapacity,
                        APS_MatData_V.dProBatch,
                        APS_MatData_V.cProMethod
                    FROM
                        APS_MatData_V
                    WHERE
                        1 = 1;");
                var APS_MatData_VRow = CommonMethod.Instance.retData(APS_MatData_V, "", "");//物料主数据

                string APS_Rules = string.Format(@"SELECT
                        cRuleName,
                        cRuleValue,
                        cIsActive 
                    FROM
                        APS_PlanConfig 
                    WHERE
                        cProductDepNo = '{0}' 
                        AND cRuleName = 'dBigOrderLimitNum';",cProductDepNo);
                var APS_RulesRow = CommonMethod.Instance.retData(APS_Rules, "", "");//最大批次划分规则

                //判断是否开了计时器
                CommonMethod.Instance.Global_Start();
                var manuBOM = CommonMethod.APS_ManuBOM;
                var routing = CommonMethod.APS_Routing;

                var PlanOrderObj = mOrderStructural.Instance.getPOrderObj(APS_PlanOrderRow);
                var reworkObj = mOrderStructural.Instance.getReworkObj(APS_ReworkRow);
                var RoutingObj = mOrderStructural.Instance.getRoutingObj(routing);
                var ResObj = mOrderStructural.Instance.getResObj(APS_ResourceRow);
                var ResObj2 = mOrderStructural.Instance.getResObj2(APS_ResourceRow);
                var ResGroupObj = mOrderStructural.Instance.getResGroup(APS_ResGroupRow);
                var ResCalendarObj = mOrderStructural.Instance.getWorkCal(APS_WorkCalendarRow);
                var OpeCalendarObj = mOrderStructural.Instance.getWorkCal2(APS_WorkCalendarRow);
                var ResCapObj = mOrderStructural.Instance.getResCapObj(APS_CapacityRow);
                var MainPlanObj = mOrderStructural.Instance.getMainPlan(APS_MainPlanRow); 
                var BomObj = mOrderStructural.Instance.getBOM(manuBOM,out Dictionary<string,Dictionary<string,DataRow>> outBom);
                var MatDataObj = mOrderStructural.Instance.getMatData(APS_MatData_VRow);
                var MatainPlanObj = mOrderStructural.Instance.getMaintainObj(APS_MaintenanceRow, ResObj2, ResCalendarObj);


                //1、计算当前可用资源组的产能1（扣除维保计划）
                var resCap = mOrderStructural.Instance.getResCapacity1(ResObj, ResCalendarObj, ResCapObj, MatainPlanObj);

                //2、计算当前可用资源组的产能2（扣除现存主计划，除去现在参与重计划的销售订单）
                var resCap1 = mOrderStructural.Instance.getResCapacity2(resCap, MainPlanObj, PlanOrderObj);

                //3、计算大批量订单的资源组（资源组即配比）每日安排的固定产能（扣除维保计划）
                var bigOrderCap = mOrderStructural.Instance.getBigOrderCap(ResGroupObj, ResObj, ResCapObj, ResCalendarObj, MatainPlanObj);

                //4、获取钉卷工序资源组的最早可用日期(根据现存主计划,除去现在参与重计划的销售订单)
                var resStartDateObj = mOrderStructural.Instance.getResGroupStartDate(MainPlanObj, PlanOrderObj);

                //5、排主计划
                var planRes = mOrderStructural.Instance.getMainPlanCal(APS_RulesRow, APS_PlanOrderRow, reworkObj, RoutingObj, BomObj, outBom, OpeCalendarObj, resStartDateObj, resCap1, bigOrderCap, MatDataObj);

                //写入数据库
                mainOrderUpdate.Instance.updateMainPlan(planRes, MainPlanObj, cProductDepNo);

                #region 算物料总需求
                string APS_PlanOrder1 = string.Format(@"SELECT DISTINCT 
                        p.cPOrderNo,
                        p.cMatNo,
                        p.dPlanQty as dNetQty,
                        p.dPlanQty1 as dNetQty1,
                        s.dNetQty2,
                        s.dNetQty3,
                        p.AutoID,
                        p.cStatusNo,
                        p.cReworkFlag,
                        m.cProductDepNo
                    FROM
                        APS_PlanOrder p
                        LEFT JOIN APS_PlanOandSaleORelat ps ON p.cPOrderNo = ps.cPOrderNo
                        LEFT JOIN APS_SOrder s ON s.cSONo = ps.cSourceNo
                        LEFT JOIN APS_MatData_V m ON m.cMatNo = s.cMatNo 
                    WHERE
                        m.cProductDepNo = '{0}'
                        AND p.cStatusNo  = '0030'
                        AND p.cReworkFlag = '0';", cProductDepNo);
                var APS_PlanOrder1Row = CommonMethod.Instance.retData(APS_PlanOrder1, "", "");

                var BomObj1 = TotalStructural.Instance.getBOM(manuBOM);
                var MatDataObj1 = TotalStructural.Instance.getMatData(APS_MatData_VRow);
                var RoutingObj1 = TotalStructural.Instance.getRouting(routing);

                var TotalQtyObj = TotalStructural.Instance.getSorderMatTotalQty(APS_PlanOrder1Row, BomObj1, MatDataObj1, RoutingObj1);
                // 物料总需求对象
                var cMatTotalQtyObj = TotalQtyObj[0];
                // 已物料需求计划的销售订单
                var cSONoArr_old = TotalQtyObj[1];
                // 半成品物料占用表
                //let MatStockDetailArr = TotalQtyObj[2];
                var cSONoArr_new = TotalQtyObj[2];

                // 删除已物料需求计划的销售订单在ValidBOM表中的信息
                totalUpdate.Instance.deleteSoNo(cSONoArr_old);
                totalUpdate.Instance.deleteSoNo(cSONoArr_new);
                // 更新销售订单的状态
                totalUpdate.Instance.updateSoNo(cSONoArr_new);

                // 更新物料子件总需求
                totalUpdate.Instance.insertMatTotalQty(cMatTotalQtyObj);
                #endregion
                list.Add("success");
            }
            catch (Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        } 

        //单独执行物料需求
        public List<string> sumOrder(string cProductDepNo)
        {
            List<string> list = new List<string>();
            try
            {
                CommonMethod.Instance.Global_Start();
                var manuBOM = CommonMethod.APS_ManuBOM;
                var routing = CommonMethod.APS_Routing;

                string APS_MatData_V = string.Format(@"SELECT
                        APS_MatData_V.cMatNo,
                        APS_MatData_V.cUnit,
                        APS_MatData_V.dwidth,
                        APS_MatData_V.dVoltage,
                        APS_MatData_V.Unit,
                        APS_MatData_V.cMatName,
                        APS_MatData_V.cSpecifucation,
                        APS_MatData_V.cCateNo,
                        APS_MatData_V.cCapacity,
                        APS_MatData_V.dProBatch,
                        APS_MatData_V.cProMethod
                    FROM
                        APS_MatData_V
                    WHERE
                        1 = 1;");
                var APS_MatData_VRow = CommonMethod.Instance.retData(APS_MatData_V, "", "");//物料主数据

                string APS_PlanOrder1 = string.Format(@"SELECT DISTINCT 
                        p.cPOrderNo,
                        p.cMatNo,
                        p.dPlanQty as dNetQty,
                        p.dPlanQty1 as dNetQty1,
                        s.dNetQty2,
                        s.dNetQty3,
                        p.AutoID,
                        p.cStatusNo,
                        p.cReworkFlag,
                        m.cProductDepNo
                    FROM
                        APS_PlanOrder p
                        LEFT JOIN APS_PlanOandSaleORelat ps ON p.cPOrderNo = ps.cPOrderNo
                        LEFT JOIN APS_SOrder s ON s.cSONo = ps.cSourceNo
                        LEFT JOIN APS_MatData_V m ON m.cMatNo = s.cMatNo 
                    WHERE
                        m.cProductDepNo = '{0}'
                        AND p.cStatusNo  = '0030'
                        AND p.cReworkFlag = '0';", cProductDepNo);

                var APS_PlanOrder1Row = CommonMethod.Instance.retData(APS_PlanOrder1, "", "");

                var BomObj1 = TotalStructural.Instance.getBOM(manuBOM);
                var MatDataObj1 = TotalStructural.Instance.getMatData(APS_MatData_VRow);
                var RoutingObj1 = TotalStructural.Instance.getRouting(routing);

                var TotalQtyObj = TotalStructural.Instance.getSorderMatTotalQty(APS_PlanOrder1Row, BomObj1, MatDataObj1, RoutingObj1);
                // 物料总需求对象
                var cMatTotalQtyObj = TotalQtyObj[0];
                // 已物料需求计划的销售订单
                var cSONoArr_old = TotalQtyObj[1];
                // 半成品物料占用表
                //let MatStockDetailArr = TotalQtyObj[2];
                var cSONoArr_new = TotalQtyObj[2];

                // 删除已物料需求计划的销售订单在ValidBOM表中的信息
                totalUpdate.Instance.deleteSoNo(cSONoArr_old);
                totalUpdate.Instance.deleteSoNo(cSONoArr_new);
                // 更新销售订单的状态
                totalUpdate.Instance.updateSoNo(cSONoArr_new);

                // 更新物料子件总需求
                totalUpdate.Instance.insertMatTotalQty(cMatTotalQtyObj);
            }
            catch(Exception e)
            {

            }
            return list;
        }
    }
}